-- =============================================
-- Procedure to get all the files for the specified Content.
--
-- Modifications
-- 06/27/2003     E.Tatsui   Created
-- =============================================


CREATE       PROCEDURE amsp_CMGetContentFiles
  @InContentID numeric = 0 ,
  @InContentFileID numeric = 0
AS
BEGIN
  SELECT a.ContentID,
         a.ContentFileID,
         a.FileName,
         a.SourceLocation,
         a.PublishLocation,
         a.Description,
         b.MimeType,
         b.FileTypeCode,
         b.FileTypeDesc,
         c.OpenInNewWindowFlag,
         CASE WHEN c.MembersOnlyFlag = 'Y' THEN e.ProtectedRootPath
              ELSE e.PublishRootPath END AS PublishRootPath,
         CASE WHEN d.NavContentGroupInd = 'N' THEN
              REPLACE(a.PublishLocation,REPLACE(IsNull(e.NavPublishDirectory,''),'\','/'), IsNull(e.NavContentDisplayPath,''))
         ELSE
              REPLACE(a.PublishLocation,REPLACE(IsNull(e.ContentFolderPublishDirectory,''),'\','/'), IsNull(e.FolderContentDisplayPath,''))
         END AS DisplayPublishLocation
    FROM Content_File a,
         File_Type_Ref b,
                 Content c, Nav_Menu d
    LEFT OUTER JOIN Website e
      ON d.WebsiteKey = e.WebsiteKey
   WHERE a.FileTypeCode = b.FileTypeCode
         AND a.ContentID = c.ContentID
     AND c.NavMenuID = d.NavMenuID
     AND a.ContentID = CASE WHEN @InContentID > 0 THEN @InContentID
                            ELSE a.ContentID END
     AND a.ContentFileID =  CASE WHEN @InContentFileID > 0 THEN @InContentFileID
                                 ELSE a.ContentFileID END
        ORDER by a.SortOrder

END

GO
GRANT EXECUTE ON  [dbo].[amsp_CMGetContentFiles] TO [IMIS]
GO